﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Data.SqlClient;

namespace ExcelCarbonTracker2010
{
    public partial class ThisAddIn
    {
        SqlConnection cnx;
        InsertDataSet.UnitProductionDataTable table;
        InsertDataSet.TestingDataTable tableMissingData;

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }
        public void ImportData()
        {
            this.Application.Cursor = Excel.XlMousePointer.xlWait;
            Excel.Workbook wb = Application.ActiveWorkbook;
            this.cnx = new DatabaseInterface.Connection().getConnection();
            this.table = new InsertDataSet.UnitProductionDataTable();
            this.tableMissingData = new InsertDataSet.TestingDataTable();

            frmProgress prog = new frmProgress();
            prog.prgBar.Minimum = 1;
            prog.prgBar.Maximum = wb.Worksheets.Count + 1;
            prog.prgBar.Value = 1;
            prog.Show();

            
            foreach (Excel.Worksheet ws in wb.Worksheets)
            {
                prog.lblData.Text = "Processing " + ws.Name;
                int i = 1;
                int j = 1;
                string powerUnitName;
                while (j < 8)
                {
                    if (ws.get_Range("A" + (i + j).ToString()).Value == "Fecha")
                    {
                        i = i + j - 1;
                        powerUnitName = ws.get_Range("A" + i).Value;
                        i = GetPowerUnitProduction(i + 2, powerUnitName, ws);
                        j = 1;
                    }
                    j++;
                }
                prog.prgBar.Value = prog.prgBar.Value + 1;
            }
            this.Application.Cursor = Excel.XlMousePointer.xlDefault;
            
            prog.lblData.Text = "Uploading Data";

            if (this.tableMissingData.Rows.Count == 0)
            {
                InsertData();
            }
            else
            {
                System.Windows.Forms.DialogResult result;
                result = System.Windows.Forms.MessageBox.Show(GetMissingMessage(),"Warning",System.Windows.Forms.MessageBoxButtons.YesNo);
                if (result == System.Windows.Forms.DialogResult.Yes)
                {
                    InsertData();
                }
            }
            prog.Close();
        }
        private string GetMissingMessage()
        {
            string message = "Some data could not be found in the database. Should data import proceed?";
            message += "\rMissing Data:";
            foreach (InsertDataSet.TestingRow r in this.tableMissingData.Rows)
            {
                message += "\r" + r.Name + " - " + r.Desc; 
            }
            return message;
        }
        private int GetPowerUnitProduction(int startRow, string unitName, Excel.Worksheet ws)
        {
            DateTime date;
            double production = 0;
            int i = startRow;
            string inOut = unitName.Substring(unitName.Length - 1, 1);
            string name = unitName.Substring(0, unitName.Length - 1);
            string prueba = Convert.ToString(ws.get_Range("A" + i).Value2);
            while (Convert.ToString(ws.get_Range("A" + i).Value2) != null)
            {
                date = Convert.ToDateTime(ws.get_Range("A" + i).Value);
                for (int j = 1; j < 25; j++)
                {
                    production = Convert.ToDouble(ws.get_Range("A" + i).get_Offset(0,j+1).Value2);
                    if (!InsertRow(name, date, j, inOut, production))
                    {
                        string filter = "Name = '" + name + "'";
                        System.Data.DataRow[] selectedRows = this.tableMissingData.Select(filter);
                        if (selectedRows.Length == 0)
                        {
                            this.tableMissingData.AddTestingRow(name, ws.Name);
                        }
                    }
                }
                i++;
                prueba = Convert.ToString(ws.get_Range("A" + i).Value2);
            }
            return i;
        }
        
        private bool InsertRow(String powerUnitName, DateTime date, int hour, String inOut, double production)
        {
            int unitId = 0;
            BaseUnits.Builders.PowerUnitBuilder pBuilder = new BaseUnits.Builders.PowerUnitBuilder(new DatabaseInterface.Connection());
            unitId = pBuilder.GetUnitId(powerUnitName);
            bool unitExists = false;

            if (unitId != -1)
            {
                unitExists = true;
                string filter = "UnitName = '" + unitId + "'";
                filter = filter + " AND Date = '" + date.ToString() + "' AND hour = '" + hour + "'";
                System.Data.DataRow[] selectedRows = this.table.Select(filter);
                if (selectedRows.Length == 0)
                {
                    if (inOut.ToUpper() == "E")
                    {
                        table.Rows.Add(new object[7] { unitId, date, hour, production,0, null, null });
                    }
                    else
                    {
                        table.Rows.Add(new object[7] { unitId, date, hour, 0, production, null, null });
                    }
                }
                else
                {
                    if (inOut.ToUpper() == "E")
                    {
                        selectedRows[0]["Incoming"] = production;
                    }
                    else
                    {
                        selectedRows[0]["Outgoing"] = production;
                    }
                }
            }
            return unitExists;
        }

        private void InsertData()
        {
            SqlBulkCopy bulk = new SqlBulkCopy(cnx);

            bulk.DestinationTableName = "PowerUnitProduction";
            bulk.ColumnMappings.Add("UnitName", "PowerUnitId");
            bulk.ColumnMappings.Add("Date", "Date");
            bulk.ColumnMappings.Add("Hour", "Hour");
            bulk.ColumnMappings.Add("Incoming", "Incoming");
            bulk.ColumnMappings.Add("Outgoing", "Outgoing");
            bulk.ColumnMappings.Add("Fuel", "FuelId");
            bulk.ColumnMappings.Add("FuelQuantity", "FuelQuantity");

            try
            {
                cnx.Open();
                bulk.WriteToServer(table);
                cnx.Close();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Please check that data hasn't already been uploaded and that all power units have been created\n\r\r\n" + ex.Message,"Error loading data",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Warning);
            }
        }
        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}
